/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;


import dto.DepartmentDto;
import dto.EmployeeDto;
import dto.RoleDto;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import util.Constant;

/**
 *
 * @author ADMIN
 */
public class EmployeeDaoImpl extends BaseDao {
    
    public ArrayList<EmployeeDto> getListEmployee(String name, int deptID) {
        ArrayList<EmployeeDto> list = new ArrayList<EmployeeDto>();
        if (getConnect()) {
            StringBuilder sql = new StringBuilder("Select * from Employee");
            if (deptID > 0 && (name != null && !"".equals(name))) {
                sql.append(" WHERE EmployeeName = ? AND deptID = ?");
            } else if (deptID > 0) {
                sql.append(" WHERE deptID = ?");
            } else if (name != null && !"".equals(name)) {
                sql.append(" WHERE EmployeeName = ?");
            }
            try {
                preparedStatement = connection.prepareStatement(sql.toString());
                if (deptID > 0 && (name != null && !"".equals(name))) {
                    preparedStatement.setString(1, name);
                    preparedStatement.setInt(2, deptID);
                } else if (deptID > 0) {
                   preparedStatement.setInt(1, deptID);
                } else if (name != null && !"".equals(name)) {
                    preparedStatement.setString(1, name);
                }
                
                resultSet = preparedStatement.executeQuery();
                
                while (resultSet.next()) {
                   EmployeeDto userDto = new EmployeeDto();
                   userDto.setEmployeeID(resultSet.getInt("EmployeeID"));
                   userDto.setUserName(resultSet.getString("UserName"));
                   userDto.setEmployeeName(resultSet.getString("EmployeeName"));
                   userDto.setPassword(resultSet.getString("password"));
                   userDto.setEmailID(resultSet.getString("emailID"));
                   userDto.setBirthday(resultSet.getString("birthday"));
                   userDto.setGender(resultSet.getInt("Gender"));
                   userDto.setAddress(resultSet.getString("address"));
                   userDto.setDeptID(resultSet.getInt("deptID"));
                   userDto.setRoleID(resultSet.getInt("roleID"));
                   userDto.setManagerID(resultSet.getInt("ManagerID"));
                   userDto.setImage(resultSet.getString("Images"));
                   
                    list.add(userDto);
                }
                
            } catch (SQLException ex) {
                JOptionPane.showMessageDialog(null,ex.getMessage(), "Error", JOptionPane.WARNING_MESSAGE);
            }
            
        }
        disconnect();
        return list;
    }
    /**
     * 
     * @param user
     * @param password
     * @return 
     */
    public EmployeeDto getEmployeeByUserName(String user, String password) {
        EmployeeDto userDto = new EmployeeDto();
        if (getConnect()) {
            StringBuilder sql = new StringBuilder("Select e.EmployeeID, e.userName, e.EmployeeName, e.password,");
            sql.append("e.emailID, e.birthday, e.gender, e.address, e.deptID, e.roleID, e.managerID, e.images,");
            sql.append("d.departmentName, r.roleName");
            sql.append(" from ((Employee e join department d");
            sql.append(" ON e.deptID = d.deptID)");
            sql.append(" join role r ON e.roleID = r.roleID)");
            sql.append(" where username = ? and password = ?");
            try {
                preparedStatement = connection.prepareStatement(sql.toString());
                preparedStatement.setString(1, user);
                preparedStatement.setString(2, password);
                
                resultSet = preparedStatement.executeQuery();
                
                if (resultSet != null) {
                   resultSet.next();
                   userDto.setEmployeeID(resultSet.getInt("EmployeeID"));
                   userDto.setUserName(resultSet.getString("UserName"));
                   userDto.setEmployeeName(resultSet.getString("EmployeeName"));
                   userDto.setPassword(resultSet.getString("password"));
                   userDto.setEmailID(resultSet.getString("emailID"));
                   userDto.setBirthday(resultSet.getString("birthday"));
                   userDto.setGender(resultSet.getInt("Gender"));
                   userDto.setAddress(resultSet.getString("address"));
                   userDto.setDeptID(resultSet.getInt("deptID"));
                   userDto.setRoleID(resultSet.getInt("roleID"));
                   userDto.setManagerID(resultSet.getInt("ManagerID"));
                   userDto.setImage(resultSet.getString("Images"));
                   userDto.setDepartmentName(resultSet.getString("departmentName"));
                   userDto.setRoleName(resultSet.getString("roleName"));
                   disconnect();
                    return userDto;
                }
                
            } catch (SQLException ex) {
                JOptionPane.showMessageDialog(null,ex.getMessage(), "Error", JOptionPane.WARNING_MESSAGE);
            }
            
        }
        disconnect();
        return null;
       
    }
    
    /**
     * 
     * @param user
     * @param password
     * @return 
     */
    public EmployeeDto getEmployeeByID(int empID) {
        EmployeeDto userDto = new EmployeeDto();
        if (getConnect()) {
            String sql = "Select * from Employee where EmployeeID = ?";
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1, empID);
                
                resultSet = preparedStatement.executeQuery();
                
                if (resultSet != null) {
                   resultSet.next();
                   userDto.setEmployeeID(resultSet.getInt("EmployeeID"));
                   userDto.setUserName(resultSet.getString("UserName"));
                   userDto.setEmployeeName(resultSet.getString("EmployeeName"));
                   userDto.setPassword(resultSet.getString("password"));
                   userDto.setEmailID(resultSet.getString("emailID"));
                   userDto.setBirthday(resultSet.getString("birthday"));
                   userDto.setGender(resultSet.getInt("Gender"));
                   userDto.setAddress(resultSet.getString("address"));
                   userDto.setDeptID(resultSet.getInt("deptID"));
                   userDto.setRoleID(resultSet.getInt("roleID"));
                   userDto.setManagerID(resultSet.getInt("ManagerID"));
                   userDto.setImage(resultSet.getString("Images"));
                   
                    return userDto;
                }
                
            } catch (SQLException ex) {
                JOptionPane.showMessageDialog(null,ex.getMessage(), "Error", JOptionPane.WARNING_MESSAGE);
            }
            
        }
        disconnect();
        return null;
       
    }
    
    public boolean insertEmployee(EmployeeDto dto) {
        boolean result = true;
        
         if (getConnect()) {
            StringBuilder sql = new StringBuilder("INSERT INTO employee VALUES (?,?,?,?,?,?,?,?,?,?,?)");
            try {
                int index = 1;
                System.out.println(dto.getEmployeeID()+";"+dto.getUserName()+";"+dto.getEmployeeName()+";"+dto.getPassword()+";"+ dto.getEmailID()+";"+dto.getBirthday()+";"+dto.getManagerID()+";"+dto.getRoleID());
                preparedStatement = connection.prepareStatement(sql.toString());
                preparedStatement.setString(index++, dto.getUserName());
                preparedStatement.setString(index++, dto.getEmployeeName());
                preparedStatement.setString(index++, dto.getPassword());
                preparedStatement.setString(index++, dto.getEmailID());
                preparedStatement.setString(index++, dto.getBirthday());
                preparedStatement.setInt(index++, dto.getGender());
                preparedStatement.setString(index++, dto.getAddress());
                preparedStatement.setInt(index++, dto.getDeptID());
                preparedStatement.setInt(index++, dto.getRoleID());
                preparedStatement.setInt(index++, dto.getManagerID());
                preparedStatement.setString(index++, dto.getImage());
                result = preparedStatement.execute();
                
            } catch (SQLException ex) {
                result = false;
                JOptionPane.showMessageDialog(null,ex.getMessage(), "Error", JOptionPane.WARNING_MESSAGE);
            }
            
        }
         disconnect();
        return result;
    }
    
    public boolean updateEmployee(EmployeeDto dto) {
        boolean result = true;
        
         if (getConnect()) {
            StringBuilder sql = new StringBuilder("UPDATE employee set (userName = ?, employeeName = ?, password = ?, ");
            sql.append("emailID = ?, birthday = ?, gender = ?, address = ?, deptID = ?, roleID = ?, managerID = ?, image = ?)");
            sql.append(" WHERE employeeID = ?");
            try {
                int index = 1;
                preparedStatement = connection.prepareStatement(sql.toString());
                preparedStatement.setString(index++, dto.getUserName());
                preparedStatement.setString(index++, dto.getEmployeeName());
                preparedStatement.setString(index++, dto.getPassword());
                preparedStatement.setString(index++, dto.getEmailID());
                
                preparedStatement.setString(index++,dto.getBirthday());
                preparedStatement.setInt(index++, dto.getGender());
                preparedStatement.setString(index++, dto.getAddress());
                preparedStatement.setInt(index++, dto.getDeptID());
                preparedStatement.setInt(index++, dto.getRoleID());
                preparedStatement.setInt(index++, dto.getManagerID());
                preparedStatement.setString(index++, dto.getImage());
                
                preparedStatement.setInt(index++, dto.getEmployeeID());
                result = preparedStatement.execute();
                
            } catch (SQLException ex) {
                result = false;
                JOptionPane.showMessageDialog(null,ex.getMessage(), "Error", JOptionPane.WARNING_MESSAGE);
            }
            
        }
         disconnect();
        return result;
    }
    
    public boolean deleteEmployee(int employeeID) {
        boolean result = true;
        if (getConnect()) {
            StringBuilder sql = new StringBuilder("DELETE Employee WHERE EmployeeID =?");
            
            try {
                int index = 1;
                preparedStatement = connection.prepareStatement(sql.toString());
               
                preparedStatement.setInt(index++, employeeID);
                result = preparedStatement.execute();
                
            } catch (SQLException ex) {
                result = false;
                JOptionPane.showMessageDialog(null,ex.getMessage(), "Error", JOptionPane.WARNING_MESSAGE);
            }
            
        }
        return result;
    }
    /**
     * 
     * @return 
     */    
    public ArrayList<RoleDto> getListRole() {
        ArrayList<RoleDto> list = new ArrayList<RoleDto> ();
        if (getConnect()){
            String sql = "SELECT * FROM Role";
            try {
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
                
                if (resultSet != null) {
                    while  (resultSet.next()) {
                        RoleDto role = new RoleDto();
                        role.setRoleId(resultSet.getInt("RoleID"));
                        role.setRoleName(resultSet.getString("RoleName"));
                        role.setDescribe(resultSet.getString("Describe"));
                        
                        list.add(role);
                    }
                }
            } catch (SQLException ex) {
                Logger.getLogger(EmployeeDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            
        }
        disconnect();
        return list;
    }
    
    public RoleDto getRoleByID (int roleID) {
        if (getConnect()) {
            String sql = "SELECT * FROM Role WHERE roleID = ?";
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1, roleID);
                
                resultSet = preparedStatement.executeQuery();
                
                if (resultSet.next()) {
                   RoleDto dto = new RoleDto();
                   dto.setRoleId(resultSet.getInt("roleID"));
                   dto.setRoleName(resultSet.getString("RoleName"));
                   dto.setDescribe(resultSet.getString("describe"));
                   return dto;
                }
            } catch (SQLException ex) {
                Logger.getLogger(EmployeeDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            
            
        }
        disconnect();
        
        return null;
    }
    
    public RoleDto getRoleByName (String roleName) {
        if (getConnect()) {
            String sql = "SELECT * FROM Role WHERE RoleName = ?";
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, roleName);
                
                resultSet = preparedStatement.executeQuery();
                
                if (resultSet.next()) {
                   RoleDto dto = new RoleDto();
                   dto.setRoleId(resultSet.getInt("roleID"));
                   dto.setRoleName(resultSet.getString("RoleName"));
                   dto.setDescribe(resultSet.getString("describe"));
                   return dto;
                }
            } catch (SQLException ex) {
                Logger.getLogger(EmployeeDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            
            
        }
        disconnect();
        
        return null;
    }
    
    public DepartmentDto getDepartmentByID(int DeptId){
        if(getConnect()){
            try {
                String sql = "SELECT * FROM Department where DeptID = ?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1, DeptId);
                resultSet = preparedStatement.executeQuery();
                if(resultSet.next()){
                    DepartmentDto dto = new DepartmentDto();
                    dto.setDeptID(resultSet.getInt("DeptID"));
                    dto.setDepartmentName(resultSet.getString("DepartmentName"));
                    dto.setDescribe(resultSet.getString("Describe"));
                    return dto;
                }
            } catch (SQLException ex) {
                Logger.getLogger(EmployeeDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        disconnect();
        return null;
    }
    
    public DepartmentDto getDepartmentByName(String deptName){
        if(getConnect()){
            try {
                String sql = "SELECT * FROM Department where DepartmentName = ?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, deptName);
                resultSet = preparedStatement.executeQuery();
                if(resultSet.next()){
                    DepartmentDto dto = new DepartmentDto();
                    dto.setDeptID(resultSet.getInt("DeptID"));
                    dto.setDepartmentName(resultSet.getString("DepartmentName"));
                    dto.setDescribe(resultSet.getString("Describe"));
                    return dto;
                }
            } catch (SQLException ex) {
                Logger.getLogger(EmployeeDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        disconnect();
        return null;
    }
    
     public ArrayList<DepartmentDto> getListDepartment() {
         ArrayList<DepartmentDto> list = new ArrayList<DepartmentDto> ();
        if (getConnect()){
            String sql = "SELECT * FROM Department";
            try {
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
                if (resultSet != null) {
                    while  (resultSet.next()) {
                        DepartmentDto department = new DepartmentDto();
                        department.setDeptID(resultSet.getInt("deptID"));
                        department.setDepartmentName(resultSet.getString("departmentName"));
                        department.setDescribe(resultSet.getString("describe"));
                        list.add(department);
                    }
                }
            } catch (SQLException ex) {
                Logger.getLogger(EmployeeDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        disconnect();
        return list;
     }
     public ArrayList<EmployeeDto> getListManager() {
        ArrayList<EmployeeDto> list = new ArrayList<EmployeeDto> ();
        if (getConnect()){
            String sql = "SELECT * FROM Employee where RoleId = 2";
            try {
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
                
                if (resultSet != null) {
                    while  (resultSet.next()) {
                        EmployeeDto manager = new EmployeeDto();
                        manager.setEmployeeID(resultSet.getInt("EmployeeID"));
                        manager.setUserName(resultSet.getString("UserName"));
                        manager.setEmployeeName(resultSet.getString("EmployeeName"));
                        manager.setPassword(resultSet.getString("password"));
                        manager.setEmailID(resultSet.getString("emailID"));
                        manager.setBirthday(resultSet.getString("birthday"));
                        manager.setGender(resultSet.getInt("Gender"));
                        manager.setAddress(resultSet.getString("address"));
                        manager.setDeptID(resultSet.getInt("deptID"));
                        manager.setRoleID(resultSet.getInt("roleID"));
                        manager.setManagerID(resultSet.getInt("ManagerID"));
                        manager.setImage(resultSet.getString("Images"));
                        list.add(manager);
                    }
                }
            } catch (SQLException ex) {
                Logger.getLogger(EmployeeDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            
        }
        disconnect();
        return list;
     }
     
}
